package db

import (
	db "hulk-api/databases"
	"log"
	"strconv"
	"fmt"
	"io/ioutil"
	"mime/multipart"
	"os"
	"io"
	"strings"
	"errors"
)
//sql job 任务执行结构


type Job struct {
	Id int 		`json:"id"`
	Createtime string		`json:"createtime"`
	Submitter string		`json:"submitter"`
	Department string		`json:"department"`
	Dbname string		`json:"dbname"`
	Statement string			`json:"statement"`
	Status int				`json:"status"`
	Result string				`json:"result"`

	SqlType string			`json:"type"`	//ddl,dml
	method string		`json:"method"`	//edit upload
}

type JobSummary struct {
	Dbname string 				`json:"dbname"`
	Undeal []Job				`json:"undeal"`
	Success []Job				`json:"success"`
	Failed  []Job				`json:"failed"`
}



//sql job 任务执行结构
type SqlJob struct {
	Id int 						`json:"id"`
	Dbname string				`json:"dbname"`
	Statement string			`json:"statement"`
	SqlPath string 				`json:"sqlpath"`
	Instance string				`json:"instance"`
}

//查询任务
type Select struct {
	Instance string		`json:"instance"`
	Dbname string		 `json:"dbname"`
	Statement string		`json:"statement"`
}


func (job Job) GetBrotherJobs() (  *JobSummary ) {
	return &JobSummary{
		Dbname: job.Dbname,
	}
}


func (js *JobSummary) UndealJobs() (*JobSummary)  {
	rows, err := db.SqlDB.Query("select id,createTime,submitter,department,dbname,statement from sqlJob where status=0 and dbname=? order by id desc" , js.Dbname)
	if err != nil {
		log.Println(err)
	}
	defer rows.Close()

	for rows.Next() {
		var l Job
		rows.Scan(&l.Id,&l.Createtime,&l.Submitter,&l.Department,&l.Dbname,&l.Statement,)
		js.Undeal = append(js.Undeal, l)
	}
	if err = rows.Err(); err != nil {
		log.Println(err)
		return js
	}
	return js
}

func (js *JobSummary) SuccessJobs() (*JobSummary) {

	rows, err := db.SqlDB.Query("select id,result,statement from sqlJob where status=1 and dbname=? order by id desc" , js.Dbname)
	if err != nil {
		log.Println(err)
	}
	defer rows.Close()
	for rows.Next() {
		var l Job
		rows.Scan(&l.Id,&l.Result,&l.Statement)
		js.Success = append(js.Success, l)
	}
	if err = rows.Err(); err != nil {
		return js
	}
	return js
}

func (js *JobSummary) FailedJobs() (*JobSummary) {

	rows, err := db.SqlDB.Query("select id,result,statement from sqlJob where status=2 and dbname=? order by id desc"  , js.Dbname)
	if err != nil {
		log.Println(err)
	}
	defer rows.Close()

	for rows.Next() {
		var l Job
		rows.Scan(&l.Id,&l.Result,&l.Statement)
		js.Failed = append(js.Failed, l)
	}
	if err = rows.Err(); err != nil {
		return js
	}
	return js
}


func (job *Job) UpdateStatus() (ra int64, err error) {

	stmt, err := db.SqlDB.Prepare("update sqlJob set status=?,result=? where id=?")
	defer stmt.Close()
	if err != nil {
		return
	}
	rs, err := stmt.Exec(job.Status, job.Result, job.Id)
	if err != nil {
		return
	}
	ra, err = rs.RowsAffected()
	return
}

//获取指定任务清单
func (job *Job) GetSqlJob() ( j SqlJob  ) {
	//var j SqlJob

	row := db.SqlDB.QueryRow("select job.id,job.dbname,db.instance from sqlJob as job , dbs as db where job.dbname = db.name and job.id = ?" , job.Id)
	if err :=row.Scan(&j.Id,&j.Dbname,&j.Instance); err != nil{
		log.Println("error is ",err)
		return
	}
	return
}

//TODO: 如何避免同一个id任务重复执行
func (job SqlJob) Do() () {

	instance := db.Instance(job.Instance)
	conn , err := instance.DbConn(job.Dbname)
	if err != nil {
		return
	}
	defer conn.Close()

	sqlpath := fmt.Sprintf("static/ddl-dml/%d.sql",job.Id)
	resultPath := fmt.Sprintf("static/ddl-dml/%d-result.txt",job.Id)
	f,err := os.OpenFile(resultPath,os.O_RDWR | os.O_CREATE|os.O_APPEND,0666)
	if err != nil {
		log.Println(err)
		return
	}
	defer f.Close()


	data, err := ioutil.ReadFile(sqlpath)
	if err != nil {
		return
	}
	for i,s := range strings.Split(string(data),";") {
		if strings.TrimSpace(s) == "" {
			continue
		}
		result, err := conn.Exec(s)
		f.WriteString(s+";\n")

		//执行失败
		if err != nil {
			err = errors.New(fmt.Sprintf("数据任务执行失败 , %s 第%d条SQL，错误：%s \n",job.Dbname , i+1,err.Error()))
			f.WriteString(err.Error())
			go db.SqlDB.Exec("update sqlJob set status = 2 , result = ? where id = ?" , err.Error() , job.Id)
			return
		}
		rowaffected,err := result.RowsAffected()

		//r = "Query OK, " + strconv.FormatInt(rowaffected,10) + " row affected"
		r := fmt.Sprintf("第%d条SQL: Query OK, %s row affected \n" ,i+1, strconv.FormatInt(rowaffected,10))
		f.WriteString(r)

	}
	go db.SqlDB.Exec("update sqlJob set status = 1 , result = ? where id = ?" , "全部执行成功，详情请见更多" , job.Id)

	return


	//result, err := conn.Exec(job.Statement)
	////执行失败
	//if err != nil {
	//	go db.SqlDB.Exec("update sqlJob set status = 2 , result = ? where id = ?" , err.Error() , job.Id)
	//	return
	//}
	//rowaffected,_ := result.RowsAffected()
	//r = "Query OK, " + strconv.FormatInt(rowaffected,10) + " row affected"
	//go db.SqlDB.Exec("update sqlJob set status = 1 , result = ? where id = ?" , r , job.Id)
	//return
}

func (job SqlJob) DoBySqlpath() ( r string , err error) {
	//var j SqlJob
	fmt.Printf("%+v\n",job)
	//获取job的实例名称
	//需要数据库名不能重复
	instance := db.Instance(job.Instance)
	conn , err := instance.DbConn(job.Dbname)
	defer conn.Close()
	//获取
	data, err := ioutil.ReadFile(job.SqlPath)
	if err != nil {
		return
	}
	for i,s := range strings.Split(string(data),";") {
		if strings.TrimSpace(s) == "" {
			continue
		}
		result, errr := conn.Exec(s)
		//执行失败
		if errr != nil {
			err = errors.New(fmt.Sprintf("数据任务执行失败 , %s 第%d条SQL，错误：%s ",job.Dbname , i+1,errr.Error()))
			fmt.Println("数据任务执行失败，第N条 ， ", job.Dbname , i)    //执行过程中遇到第一条sql错误即终止
			return
		}
		rowaffected,errr := result.RowsAffected()

		r = "Query OK, " + strconv.FormatInt(rowaffected,10) + " row affected"

	}
	return


}



func (job *Job) insert()(lastId int64,err error)  {
	res, err := db.SqlDB.Exec("insert into sqlJob(createTime,submitter,department,dbname,type) values (now(),?,?,?,?)", job.Submitter,job.Department,job.Dbname,job.SqlType)
	if err != nil {
		log.Println("new db job error: " ,err.Error() )
		return
	}
	lastId,err = res.LastInsertId()
	return
}

func (job *Job) AddSqlJob(statement interface{}) ( err error) {

	lastId,err := job.insert()
	switch v :=statement.(type) {
	case string:
		sqlpath := fmt.Sprintf("static/ddl-dml/%d.sql",lastId)
		err = ioutil.WriteFile(sqlpath,[]byte(v),0666)
		if err != nil {
			fmt.Println("保存sql：",err.Error())
		}
	case multipart.File:

		sqlpath := fmt.Sprintf("static/ddl-dml/%d.sql",lastId)
		out, err := os.Create(sqlpath)
		if err != nil {
			//log.Fatal(err)
			fmt.Println(err)
		}
		defer out.Close()
		_, err = io.Copy(out, v)
		if err != nil {
			fmt.Println(err)
		}

	}


	return
}


func (s Select) Do() ( cols []string , result []map[string]string ,err error) {
	//var j SqlJob
	result = make([]map[string]string,0)

	instance := db.Instance(s.Instance)
	conn , err := instance.DbConn(s.Dbname)
	defer conn.Close()

	rows, err := conn.Query(s.Statement)

	if err != nil {
		log.Println(err)
		return
	}
	defer rows.Close()

	cols,_ = rows.Columns()

	values := make([][]byte, len(cols))
	scans := make([]interface{}, len(cols))
	for i := range values {
		scans[i] = &values[i]
	}

	for rows.Next() {
		if err = rows.Scan(scans...); err != nil {
			fmt.Println(err)
			return
		}
		row := make(map[string]string)
		for k, v := range values {
			key := cols[k]
			row[key] = string(v)
		}
		result = append(result,row)
	}
	return
}